package com.budwk.app.sqfw.services.customservice.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONObject;
import com.budwk.app.common.component.DictComponent;
import com.budwk.app.common.config.AppException;
import com.budwk.app.oss.services.SysOssService;
import com.budwk.app.sqfw.component.CommunityStreetComponent;
import com.budwk.app.sqfw.constants.CommonConstants;
import com.budwk.app.sqfw.constants.CommonForAdminConstants;
import com.budwk.app.sqfw.enums.SupportBusinessTypeEnum;
import com.budwk.app.sqfw.models.Sqfw_cenSusPaper;
import com.budwk.app.sqfw.models.Sqfw_completeflow;
import com.budwk.app.sqfw.models.Sqfw_formSummary_new;
import com.budwk.app.sqfw.services.SqfwCompleteflowService;
import com.budwk.app.sqfw.services.SqfwFormsummaryNewService;
import com.budwk.app.sqfw.services.SqfwHouseholdRegistrationInfoService;
import com.budwk.app.sqfw.services.common.SqfwCommonForAdminService;
import com.budwk.app.sqfw.services.common.SqfwCustomForAdminCommonService;
import com.budwk.app.sqfw.vo.CheckDataForAdminVO;
import com.budwk.app.sqfw.vo.GenerateExcelUrlVO;
import com.budwk.app.sqfw.vo.GenerateWordAndPdfUrlVO;
import com.budwk.app.sqfw.vo.SqfwCustomForAdminCommonVO;
import com.budwk.app.sqfwDoc.bo.ExcelCreateConfig;
import com.budwk.app.sqfwDoc.codeGeneratorV2.fileParam.CompleteFlowExcelParamForController;
import com.budwk.app.sqfwDoc.codeGeneratorV2.generate.ResultOfExcelGenerator;
import com.budwk.app.sqfwV2.dto.entity.SqfwHouseholdRegistrationInfo;
import com.budwk.app.sqfwV2.dto.povertyMonitoring.PovertyAlleviationDTO;
import com.budwk.app.sqfwV2.enums.EntrepreneurHouseFileEnum;
import com.budwk.app.sqfwV2.enums.povertyMonitoring.FormingMaterialEnum;
import com.budwk.app.sqfwV2.service.entrepreneurShipHouse.EntrepreneurShipHouseService;
import com.budwk.app.sqfwV2.service.povertyMonitoring.PovertyMonitoringExcelService;
import com.budwk.app.sys.models.Sys_user;
import com.budwk.app.sys.services.SysLogService;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.util.adminUtils.CommonCheckUtils;
import com.budwk.app.util.poiUtils.CommonFormOfficeUtils;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.page.Pagination;
import com.budwk.starter.common.result.ResultCode;
import com.budwk.starter.security.utils.SecurityUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.nutz.aop.interceptor.ioc.TransAop;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Record;
import org.nutz.dao.entity.annotation.Table;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.aop.Aop;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.Strings;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.stream.Collectors;

/**
 * @program: dfxy-community
 * @description: 扶贫监测服务实现
 * @author: LJT
 * @create: 2024-06-17 10:11
 **/
@IocBean(name = "PovertyMonitorForAdminService")
@Slf4j
public class PovertyMonitorForAdminServiceImpl extends PovertyMonitoringExcelService implements SqfwCustomForAdminCommonService {
    @Inject
    private CommunityStreetComponent communityStreetComponent;

    @Inject
    private SqfwFormsummaryNewService sqfwFormsummaryNewService;

    @Inject
    private SqfwCompleteflowService sqfwCompleteflowService;

    @Inject
    private SysLogService sysLogService;

    @Inject
    private SysUserService sysUserService;

    @Inject
    private EntrepreneurShipHouseService entrepreneurShipHouseService;

    @Inject
    private SysOssService sysOssService;

    @Inject
    private SqfwCommonForAdminService sqfwCommonForAdminService;

    @Inject("taskQueryExecutor")
    private ThreadPoolExecutor taskQueryExecutor;

    @Override
    public Pagination list(SqfwCustomForAdminCommonVO bean) {
        if (StringUtils.isEmpty(bean.getType())) {
            throw new AppException(ResultCode.NOT_FOUND.getMsg());
        }
        Pagination pagination = queryHouseInfoPage(bean);
        long startTime = System.currentTimeMillis();
        // 构造查询条件
        Criteria criteria = buildQueryCond(bean);
        log.info("PovertyMonitorForAdminServiceImpl-list-SQL: {}", criteria.toString());
       /* Pagination pagination = sqfwFormsummaryNewService.listPage(bean.getPageNo(), bean.getPageSize(), criteria);
        // 查询流程pdf
        List<Sqfw_formSummary_new> sqfwCustomForAdminCommonVOList = pagination.getList(Sqfw_formSummary_new.class);
        if (ObjectUtil.isNotEmpty(sqfwCustomForAdminCommonVOList)) {
            List<String> ids = sqfwCustomForAdminCommonVOList.stream().map(Sqfw_formSummary_new::getId).collect(Collectors.toList());
            List<SqfwHouseholdRegistrationInfo> sqfwHouseholdRegistrationInfos = queryHouseInfoBySummaryId(ids);
            if (ObjectUtils.isNotEmpty(sqfwHouseholdRegistrationInfos)) {
                List<SqfwCustomForAdminCommonVO> finalCommonVOList = new ArrayList<SqfwCustomForAdminCommonVO>();
                Map<String, String> pdfUrlMap = CommonCheckUtils.getPdfUrlByFlowId(sqfwCustomForAdminCommonVOList);
                CompletableFuture[] cf = sqfwCustomForAdminCommonVOList.stream().map(item -> CompletableFuture.runAsync(() ->
                                processListData(item, pdfUrlMap, sqfwHouseholdRegistrationInfos, finalCommonVOList), taskQueryExecutor)
                        .thenApplyAsync(r -> r)).toArray(CompletableFuture[]::new);
                CompletableFuture.allOf(cf).join();
                pagination.setList(finalCommonVOList.stream().sorted(Comparator.comparing(SqfwCustomForAdminCommonVO::getFillDate, Comparator.reverseOrder())).collect(Collectors.toList()));
            }
        }*/
        log.info("PovertyMonitorForAdminServiceImpl-list-耗时: {}ms", System.currentTimeMillis() - startTime);
        return pagination;
    }

    @Override
    @Aop(TransAop.READ_COMMITTED)
    public int delete(String id) {
        Sqfw_formSummary_new sqfwFormSummaryNew = sqfwFormsummaryNewService.fetch(id);
        if (sqfwFormSummaryNew == null) {
            throw new AppException(ResultCode.NULL_DATA_ERROR.getMsg());
        }
        batchDeleteDataNew(SecurityUtil.getUserId(), sqfwFormSummaryNew);
        return 0;
    }

    @Override
    public int batchDelete(List<String> ids) {
        return sqfwCommonForAdminService.batchDelete(ids);
    }

    @Override
    public int update(Sqfw_formSummary_new sqfwFormsummaryNew) {
        return 0;
    }

    @Override
    public List<GenerateExcelUrlVO> downloadExcel(List<ExcelCreateConfig> excelConfigList, SqfwCustomForAdminCommonVO bean) {
        if (org.apache.commons.lang.StringUtils.isEmpty(bean.getType())) {
            throw new AppException(ResultCode.NOT_FOUND.getMsg());
        }
        List<GenerateExcelUrlVO> generateExcelUrlVOList = new ArrayList<GenerateExcelUrlVO>();
        GenerateExcelUrlVO generateExcelUrlVO = new GenerateExcelUrlVO();
        if (excelConfigList.get(0).getOnlyGenerateTemplateFlag()) {
            String filePath = FormingMaterialEnum.getBlankTemplate(Integer.valueOf(excelConfigList.get(0).getFileIdentity()));
            String fileName = FormingMaterialEnum.getFileName(Integer.valueOf(excelConfigList.get(0).getFileIdentity()));
            generateExcelUrlVO.setExcelUrl(sysOssService.upload(CommonFormOfficeUtils.getTempPdfFile(filePath), fileName).getUrl());
            generateExcelUrlVOList.add(generateExcelUrlVO);
            return generateExcelUrlVOList;
        }
        // 如果formSummaryNewIdList为空，根据条件下载，否则根据勾选id下载
        if (ObjectUtil.isEmpty(bean.getFormSummaryNewIdList()) || bean.getFormSummaryNewIdList().size() <= 0) {
            Criteria criteria = buildQueryCond(bean);
            List<Sqfw_formSummary_new> summaryNewList = sqfwFormsummaryNewService.query(criteria);
            if (summaryNewList.size() > 0) {
                bean.setFormSummaryNewIdList(sqfwFormsummaryNewService.query(criteria).stream().map(Sqfw_formSummary_new::getId).collect(Collectors.toList()));
            } else {
                return new ArrayList<GenerateExcelUrlVO>();
            }
        } else {
            Cnd cnd = Cnd.NEW();
            // 时间
            if (ObjectUtil.isNotEmpty(bean.getQueryStartTime()) && ObjectUtil.isNotEmpty(bean.getQueryEndTime())) {
                cnd.and(Sqfw_formSummary_new::getFillDate, ">=", communityStreetComponent.handleStartQueryTime(bean.getQueryStartTime()));
                cnd.and(Sqfw_formSummary_new::getFillDate, "<=", communityStreetComponent.handleEndQueryTime(bean.getQueryEndTime()));
            }
            cnd.and(Sqfw_formSummary_new::getId, "IN", bean.getFormSummaryNewIdList());
            List<Sqfw_formSummary_new> summaryNewList = sqfwFormsummaryNewService.query(cnd);
            if (summaryNewList.size() > 0) {
                bean.setFormSummaryNewIdList(summaryNewList.stream().map(Sqfw_formSummary_new::getId).collect(Collectors.toList()));
            } else {
                return new ArrayList<GenerateExcelUrlVO>();
            }
        }

        // 自动生成代码2.0版本跟新
        CompleteFlowExcelParamForController paramForController = new CompleteFlowExcelParamForController();
        paramForController.setFormSummaryIdList(bean.getFormSummaryNewIdList());
        paramForController.setExcelCreateConfigList(excelConfigList);
        paramForController.setUserId(SecurityUtil.getUserId());
        List<ResultOfExcelGenerator> excel = super.createExcelForAdmin(paramForController);
        if (CollectionUtil.isNotEmpty(excel)) {
            generateExcelUrlVO.setExcelUrl(excel.get(0).getExcelUrl());
            generateExcelUrlVOList.add(generateExcelUrlVO);
        }
        return generateExcelUrlVOList;
    }

    @Override
    public GenerateWordAndPdfUrlVO downloadPdf
            (List<ExcelCreateConfig> excelConfigList, SqfwCustomForAdminCommonVO bean) {
        if (StringUtils.isEmpty(bean.getType())) {
            throw new AppException(ResultCode.NOT_FOUND.getMsg());
        }
        GenerateWordAndPdfUrlVO generateWordAndPdfUrlVO = new GenerateWordAndPdfUrlVO();
        if (excelConfigList.get(0).getOnlyGenerateTemplateFlag()) {
            String filePath = FormingMaterialEnum.getBlankTemplate(Integer.valueOf(excelConfigList.get(0).getFileIdentity()));
            String fileName = FormingMaterialEnum.getFileName(Integer.valueOf(excelConfigList.get(0).getFileIdentity()));
            generateWordAndPdfUrlVO.setPdfUrl(sysOssService.upload(CommonFormOfficeUtils.getTempPdfFile(filePath), fileName).getUrl());
            return generateWordAndPdfUrlVO;
        }
        // 如果formSummaryNewIdList为空，根据条件下载，否则根据勾勾选id下载
        if (ObjectUtil.isEmpty(bean.getFormSummaryNewIdList()) || bean.getFormSummaryNewIdList().size() <= 0) {
            Criteria criteria = buildQueryCond(bean);
            List<Sqfw_formSummary_new> summaryNewList = sqfwFormsummaryNewService.query(criteria);
            if (summaryNewList.size() > 0) {
                bean.setFormSummaryNewIdList(summaryNewList.stream().map(Sqfw_formSummary_new::getId).collect(Collectors.toList()));
            } else {
                return new GenerateWordAndPdfUrlVO();
            }
        }
        generateWordAndPdfUrlVO.setPdfUrl(generatePdfColl(bean.getFormSummaryNewIdList(), excelConfigList.get(0).getFileIdentity()));
        return generateWordAndPdfUrlVO;
    }

    @Override
    public List<GenerateExcelUrlVO> downloadPdfList
            (List<ExcelCreateConfig> excelConfigList, SqfwCustomForAdminCommonVO bean) {
        return null;
    }

    @Override
    public Criteria buildQueryCond(SqfwCustomForAdminCommonVO bean) {
        Criteria cri = Cnd.cri();
        SqlExpressionGroup criSql = cri.where();
        if (StringUtils.isEmpty(bean.getType())) {
            throw new AppException(ResultCode.NOT_FOUND.getMsg());
        }
        // 组装查询条件
        if (StringUtils.isNotBlank(bean.getPageOrderName()) && Strings.isNotBlank(bean.getPageOrderBy())) {
            if (bean.getPageOrderBy().equals(CommonConstants.ASC)) {
                cri.getOrderBy().asc(bean.getPageOrderName());
            }
            if (bean.getPageOrderBy().equals(CommonConstants.DESC)) {
                cri.getOrderBy().desc(bean.getPageOrderName());
            }
        } else {
            cri.getOrderBy().desc(Sqfw_formSummary_new::getFillDate);
        }
        // 类型
        criSql.andEquals(SqfwCustomForAdminCommonVO::getType, bean.getType());

        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (ObjectUtils.isEmpty(sysUser)) {
            throw new AppException(ResultCode.USER_NOT_FOUND.getMsg());
        }
        // 区域5级检查
        CommonCheckUtils.checkAreaRange(criSql);
        // CommonCheckUtils.checkUserDataAuth(criSql);
        return cri;
    }

    @Override
    public List<CheckDataForAdminVO> checkDataInfoList(SqfwCustomForAdminCommonVO bean) {
        List<CheckDataForAdminVO> checkDataForAdminVOList = new ArrayList<CheckDataForAdminVO>();
        for (FormingMaterialEnum item : FormingMaterialEnum.values()) {
            CheckDataForAdminVO checkDataForAdminVO = new CheckDataForAdminVO();
            checkDataForAdminVO.setFileIdentity(String.valueOf(item.getCode()));
            checkDataForAdminVO.setCreateConditionFlag(true);
            checkDataForAdminVO.setFileName(item.getFileName());
            if (item.getTemplat().endsWith("docx")) {
                checkDataForAdminVO.setFileName(checkDataForAdminVO.getFileName() + ".pdf");
            } else {
                checkDataForAdminVO.setFileName(checkDataForAdminVO.getFileName() + ".xlsx");
            }
            checkDataForAdminVOList.add(checkDataForAdminVO);
        }
        return checkDataForAdminVOList;
    }

    @Override
    public void reGeneratePdf(String id) {
        entrepreneurShipHouseService.reGeneratePDF(id);
    }

    /**
     * 生成pdf合集
     *
     * @return
     */
    private String generatePdfColl(List<String> ids, String code) {
        String url = "";
        List<Sqfw_formSummary_new> formSummaryList = sqfwFormsummaryNewService.query(Cnd.where(Sqfw_formSummary_new::getId, "IN", ids)
                .and("type", "=", SupportBusinessTypeEnum.ENTERPRENEURAL.getName()));
        if (CollectionUtil.isNotEmpty(formSummaryList)) {
            List<String> summaryIdS = formSummaryList.stream().map(Sqfw_formSummary_new::getSqfwFlowId).collect(Collectors.toList());
            List<Sqfw_completeflow> completeflow = sqfwCompleteflowService.query(Cnd.where(Sqfw_completeflow::getId, "IN", summaryIdS)
                    .and(Sqfw_completeflow::getResourcePath, "!=", "creating")
                    .and(Sqfw_completeflow::getResourcePath, "!=", "error"));
            if (CollectionUtil.isNotEmpty(completeflow)) {
                String fileName = DictComponent.getDictName(EntrepreneurHouseFileEnum.ONE_PDF.getCode(), null);
                List<String> pdfPathList = completeflow.stream().map(Sqfw_completeflow::getResourcePath).collect(Collectors.toList());
                url = CommonFormOfficeUtils.createConvergePdfAndUpload(pdfPathList, fileName);
            }
        }
        return url;
    }

    /**
     * 批量删除
     *
     * @param userId
     * @param data
     */
    private void batchDeleteDataNew(String userId, Sqfw_formSummary_new data) {
        //1 记录日志
        sysLogService.insertDelLog(userId, JSONObject.toJSONString(data), Sqfw_cenSusPaper.class.getAnnotation(Table.class).value(),
                "com.budwk.app.sqfw.services.customservice.GuanShanHuEntGuaranteeForAdminServiceImpl.batchDeleteDataNew",
                "com.budwk.app.sqfw.controllers.admin.GuanShanHuEntGuaranteeForAdminServiceImpl.batchDelete");
        // 2 删除数据
        sqfwCompleteflowService.deleteAndUpdateWO(data.getSqfwFlowId());
    }

    /**
     * 处理返回的list数据
     *
     * @param source
     * @param pdfUrlMap
     * @param finalCommonVOList
     */
    private void processListData(
            Sqfw_formSummary_new source,
            Map<String, String> pdfUrlMap,
            List<SqfwHouseholdRegistrationInfo> sqfwHouseholdRegistrationInfos,
            List<SqfwCustomForAdminCommonVO> finalCommonVOList) {
        SqfwCustomForAdminCommonVO target = new SqfwCustomForAdminCommonVO();
        BeanUtil.copyProperties(source, target);
        PovertyAlleviationDTO povertyDTO = JSONObject.parseObject(target.getParamData(), PovertyAlleviationDTO.class);
        if (StringUtils.isEmpty(povertyDTO.getIdNumber())) {
            return;
        }
        target.setPdfUrl(pdfUrlMap.get(target.getSqfwFlowId()));
        if (ObjectUtil.isNotEmpty(sqfwHouseholdRegistrationInfos)) {
            sqfwHouseholdRegistrationInfos.forEach(item -> {
                if (item.getHouseholdHeadCard().equals(povertyDTO.getIdNumber())) {
                    target.setHouseNumber(item.getHouseNumber());
                    target.setHouseType(item.getHouseType());
                }
            });
        }
        finalCommonVOList.add(target);
    }

    /**
     * 查询住户籍信息表
     */
    private List<SqfwHouseholdRegistrationInfo> queryHouseInfoBySummaryId(List<String> ids) {
        // 查询ids所有对应的身份证
        if (ObjectUtils.isEmpty(ids)) {
            return new ArrayList<SqfwHouseholdRegistrationInfo>();
        }
        SqfwHouseholdRegistrationInfoService sqfwHouseholdRegistrationInfoService
                = NutzIocUtils.getBean(SqfwHouseholdRegistrationInfoService.class);
        StringBuffer sbIds = new StringBuffer();
        for (String id : ids) {
            sbIds.append("'").append(id).append("',");
        }
        String idList = sbIds.substring(0, sbIds.length() - 1);
        idList = "(" + idList + ")";
        String sqlStr = "SELECT house_type, house_number, door_number, household_head_card FROM shequ.\"sqfw_household_registration_info\"  WHERE household_head_card IN ( SELECT param_data->>'idNumber' FROM shequ.\"sqfw_form_summary_new\" sfsn WHERE id IN " + idList + ")";
        Sql sql = Sqls.create(sqlStr);
        //sql.setParam("ids", idList);
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                List<SqfwHouseholdRegistrationInfo> sqfwHouseholdRegistrationInfos = new ArrayList<SqfwHouseholdRegistrationInfo>();
                while (rs.next()) {
                    for (int i = 0; i < columnCount; i++) {
                        SqfwHouseholdRegistrationInfo sqfwHouseholdRegistrationInfo = new SqfwHouseholdRegistrationInfo();
                        sqfwHouseholdRegistrationInfo.setHouseType(rs.getString("house_type"));
                        sqfwHouseholdRegistrationInfo.setHouseNumber(rs.getString("house_number"));
                        sqfwHouseholdRegistrationInfo.setDoorNumber(rs.getString("door_number"));
                        sqfwHouseholdRegistrationInfo.setHouseholdHeadCard(rs.getString("household_head_card"));
                        sqfwHouseholdRegistrationInfos.add(sqfwHouseholdRegistrationInfo);
                    }
                }
                return sqfwHouseholdRegistrationInfos;
            }
        });
        sqfwFormsummaryNewService.execute(sql);
        return sql.getList(SqfwHouseholdRegistrationInfo.class);
    }

    /**
     * 查询住户籍信息表
     */
    private Pagination queryHouseInfoPage(SqfwCustomForAdminCommonVO bean) {
        Pagination pagination = new Pagination();
        pagination.setPageNo(bean.getPageNo());
        pagination.setPageSize(bean.getPageSize());
        Dao dao = NutzIocUtils.getBean(Dao.class);
        // 查总条数
        String sqlStr = buildConditionForQueryStr(bean, "true");
        Sql sql = Sqls.create(sqlStr);
        sql.setCallback(Sqls.callback.integer());
        dao.execute(sql);
        pagination.setTotalCount(sql.getInt());

        // 查分页数据
        sqlStr = buildConditionForQueryStr(bean, "false");
        log.info("queryHouseInfoPage-> {}", sqlStr);
        sql = Sqls.queryRecord(sqlStr);
        dao.execute(sql);
        List<Record> records = sql.getList(Record.class);
        pagination.setList(records);

        // 处理查询出来的数据
        return pagination;
    }

    /**
     * 查询总数
     *
     * @param bean
     * @return
     */
    public int queryViewInfoCount(SqfwCustomForAdminCommonVO bean) {
        String sqlStr = buildConditionForQueryStr(bean, "true");
        Sql sql = Sqls.create(sqlStr);
        sql.setCallback(Sqls.callback.integer());
        Dao dao = NutzIocUtils.getBean(Dao.class);
        dao.execute(sql);
        return sql.getInt();
    }

    /**
     * 获取查询条件
     *
     * @param bean
     * @return
     */
    private String buildConditionForQueryStr(SqfwCustomForAdminCommonVO bean, String countFlag) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT *, CASE WHEN TO_CHAR(t1.fill_date, 'MM')::INTEGER >= 1 and TO_CHAR(t1.fill_date, 'MM')::INTEGER <= 3  THEN '2' WHEN TO_CHAR(t1.fill_date, 'MM')::INTEGER >= 4 and TO_CHAR(t1.fill_date, 'MM')::INTEGER <= 6  THEN '3' WHEN TO_CHAR(t1.fill_date, 'MM')::INTEGER >= 7 and TO_CHAR(t1.fill_date, 'MM')::INTEGER <= 9  THEN '4'  WHEN TO_CHAR(t1.fill_date, 'MM')::INTEGER >= 10 and TO_CHAR(t1.fill_date, 'MM')::INTEGER <= 12  THEN '1'  ELSE '0' END AS quarter,");
        sb.append(" TO_CHAR(t1.fill_date, 'YYYY') AS year, ");
        sb.append(" t1.id AS id, t1.param_data::text AS paramData FROM shequ.\"sqfw_form_summary_new\" t1 LEFT JOIN shequ.\"sqfw_household_registration_info\" t2  ON t2.house_number = t1.param_data ->>'houseHoldNumber' WHERE 1 = 1 ");
        if (Strings.isNotBlank(bean.getQueryAll())) {
            sb.append(" AND ( t1.province LIKE '%").append(bean.getQueryAll()).append("%'")
                    .append(" OR t1.city LIKE '%").append(bean.getQueryAll()).append("%'")
                    .append(" OR t1.district LIKE '%").append(bean.getQueryAll()).append("%'")
                    .append(" OR t1.county LIKE '%").append(bean.getQueryAll()).append("%'")
                    .append(" OR t1.community LIKE '%").append(bean.getQueryAll()).append("%')");
        }

        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (ObjectUtils.isEmpty(sysUser)) {
            throw new AppException(ResultCode.USER_NOT_FOUND.getMsg());
        }
        // 只查询提交数据
        sb.append(" AND audit_status = 'submit' ");
        // =================================检查登录用户========================================
        // 省
        if (org.apache.commons.lang.StringUtils.isNotBlank(sysUser.getProvince()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getProvince())) {
            sb.append(" AND t1.province = '").append(sysUser.getProvince()).append("' ");
        }
        // 市
        if (org.apache.commons.lang.StringUtils.isNotBlank(sysUser.getCity()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getCity())) {
            sb.append(" AND t1.city = '").append(sysUser.getCity()).append("' ");
        }
        // 区
        if (org.apache.commons.lang.StringUtils.isNotBlank(sysUser.getDistrict()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getDistrict())) {
            sb.append(" AND t1.district = '").append(sysUser.getDistrict()).append("' ");
        }
        // 街道
        if (org.apache.commons.lang.StringUtils.isNotBlank(sysUser.getCounty()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getCounty())) {
            sb.append(" AND t1.county = '").append(sysUser.getCounty()).append("' ");
        }
        // 辖区
        if (org.apache.commons.lang.StringUtils.isNotBlank(sysUser.getCommunity()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getCommunity())) {
            sb.append(" AND t1.community = '").append(sysUser.getCommunity()).append("' ");
        }
        // =================================检查输入参数========================================
        // 省
        if (org.apache.commons.lang.StringUtils.isNotBlank(bean.getProvince()) && !CommonForAdminConstants.ZHISHU.equals(bean.getProvince())) {
            sb.append(" AND t1.province = '").append(bean.getProvince()).append("' ");
        }
        // 市
        if (org.apache.commons.lang.StringUtils.isNotBlank(bean.getCity()) && !CommonForAdminConstants.ZHISHU.equals(bean.getCity())) {
            sb.append(" AND t1.city = '").append(bean.getCity()).append("' ");
        }
        // 区
        if (org.apache.commons.lang.StringUtils.isNotBlank(bean.getDistrict()) && !CommonForAdminConstants.ZHISHU.equals(bean.getDistrict())) {
            sb.append(" AND t1.district = '").append(bean.getDistrict()).append("' ");
        }
        // 街道
        if (org.apache.commons.lang.StringUtils.isNotBlank(bean.getCounty()) && !CommonForAdminConstants.ZHISHU.equals(bean.getCounty())) {
            sb.append(" AND t1.county = '").append(bean.getCounty()).append("' ");
        }
        // 辖区
        if (org.apache.commons.lang.StringUtils.isNotBlank(bean.getCommunity()) && !CommonForAdminConstants.ZHISHU.equals(bean.getCommunity())) {
            sb.append(" AND t1.community = '").append(bean.getCommunity()).append("' ");
        }

        if (StringUtils.isNotEmpty(bean.getType())) {
            sb.append(" AND t1.type = '").append(bean.getType()).append("'");
        }
        // 年度查询
        if (StringUtils.isNotEmpty(bean.getYear())) {
            sb.append(" AND TO_CHAR(t1.fill_date, 'YYYY') = '").append(bean.getYear()).append("'");
        }
        // 转移性收入是否准确
        if (StringUtils.isNotEmpty(bean.getIsCorrect())) {
            sb.append(" AND t1.param_data->>'isCorrect' = '").append(bean.getIsCorrect()).append("'");
        }
        // 季度查询
        if (StringUtils.isNotEmpty(bean.getQuarter())) {
            switch (bean.getQuarter()) {
                case "2":
                    sb.append(" AND TO_CHAR(t1.fill_date, 'MM')::INTEGER >= 1 AND TO_CHAR(t1.fill_date, 'MM')::INTEGER <= 3 ");
                    break;
                case "3":
                    sb.append(" AND TO_CHAR(t1.fill_date, 'MM')::INTEGER >= 4 AND TO_CHAR(t1.fill_date, 'MM')::INTEGER <= 6 ");
                    break;
                case "4":
                    sb.append(" AND TO_CHAR(t1.fill_date, 'MM')::INTEGER >= 7 AND TO_CHAR(t1.fill_date, 'MM')::INTEGER <= 9 ");
                    break;
                case "1":
                    sb.append(" AND TO_CHAR(t1.fill_date, 'MM')::INTEGER >= 10 AND TO_CHAR(t1.fill_date, 'MM')::INTEGER <= 12 ");
                    break;
            }
        }
        // 户编号
        if (StringUtils.isNotEmpty(bean.getHouseHoldNumber())) {
            sb.append(" AND t1.param_data->>'houseHoldNumber' = '").append(bean.getHouseHoldNumber()).append("'");
        }
        // 行政区域查询
        if(StringUtils.isNotEmpty(bean.getArea())) {
            sb.append(" AND t1.province || t1.city || t1.district || t1.county || t1.community = '").append(bean.getArea()).append("'");
        }
        if ("true".equals(countFlag)) {
            String resultStr = "";
            // 查询总数
            resultStr = "SELECT COUNT(t3.*) FROM (" + sb.toString() + ") t3";
            return resultStr;
        } else if ("false".equals(countFlag)) {
            sb.append(" ORDER BY t1.fill_date DESC");
            Integer pageNo = bean.getPageNo();
            Integer pageSize = bean.getPageSize();
            if (1 == pageNo) {
                pageNo = 0;
            }
            if (pageNo > 1) {
                pageNo = (pageNo - 1) * pageSize;
            }
            if (0 != pageSize) {
                sb.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(pageNo);
            }
        }
        return sb.toString();
    }
}
